

*****************************************************************
*****************************************************************
*** 0. General settings
*****************************************************************
*****************************************************************

* clear
clear
clear 		mata
mat 		drop _all
clear 		matrix

* set path
global		path	""			

set maxvar 32767

	
	

	
**************************************************************
**************************************************************

	* 02	Table 8 - Implications of drawdowns for lending

**************************************************************
**************************************************************

	
	
	*********************************************************coun
	* Table 8: Implications for lending 
	*********************************************************
	
	
	* load data
	*******************
	
	
	use 		"$path/0 Data/CRSP/BHC_GVKEY_Match_Daily"
	gen year = yofd(date)
	drop if year < 2019
	save		"$path/0 Data/CRSP/BHC_GVKEY_Match_Daily_20192020", replace
	
	
	
	clear
	use 		"$path/0 Data/_Processed/Master_beforecompu_exposures"
	
	gen date = dofq(exposuredate_y_q + 1) - 1
	format date %td
	
	sort gvkey date
	
	merge n:1 gvkey date using "$path/0 Data/CRSP/BHC_GVKEY_Match_Daily_20192020"
	
	keep if _merge == 3
	drop _merge
	
*merge with repayment risk Q2
*****************************
merge m:1 gvkey using "$path/0 Data/_Processed/bank_level_repayment_risk"
drop if _merge == 2
drop _merge

gen feestructure_j_scaled = feestructure_j/exposure_j
gen repay_j_scaled = repayment_j/exposure_j
gen repay_j_repayers_scaled = repayment_j_repayers/exposure_drawdown_j
gen repay_j_nonrepayers_scaled = -repayment_j_nonrepayers/exposure_j

foreach var of varlist fees_earned_* drawdown_j_* {
	replace `var' = `var'/exposure_j
}

foreach var of varlist drawdown_j* {
	gen `var'_scaled = `var'/exposure_j
}

gen repay_j_3B_scaled = repayment_j_3B/exposure_j
gen repay_j_allAs_scaled = repayment_j_allAs/exposure_j
gen repay_j_nonIG_scaled = repayment_j_nonIG/exposure_j
gen repay_j_NR_scaled = repayment_j_NR/exposure_j

gen repay_j_rep_3B_scaled = repayment_j_repayers_3B/exposure_j
gen repay_j_rep_allAs_scaled = repayment_j_repayers_allAs/exposure_j
gen repay_j_rep_nonIG_scaled = repayment_j_repayers_nonIG/exposure_j
gen repay_j_rep_NR_scaled = repayment_j_repayers_NR/exposure_j

gen repay_j_nonrep_3B_scaled = repayment_j_nonrepayers_3B/exposure_j
gen repay_j_nonrep_allAs_scaled = repayment_j_nonrepayers_allAs/exposure_j
gen repay_j_nonrep_nonIG_scaled = repayment_j_nonrepayers_nonIG/exposure_j
gen repay_j_nonrep_NR_scaled = repayment_j_nonrepayers_NR/exposure_j


** Label variables for output
label var fees_earned_j 			"Fees Earned"
label var repay_j_scaled 			"Repayments (+/-)"
label var repay_j_repayers_scaled	"Repayments (+)"
label var repay_j_nonrepayers_scaled "Additional Drawdowns (+)"
	
	
	sort entity
	
	merge n:n entity using "$path/0 Data/_Processed/Data_for_Crosssection_Test_2020Q1", keepusing(offbs_unused_loans assets_total)
	
	
	drop if _merge ==2 
	drop _merge
	
	
	rename exposuredate_y_q quarterly_date
	sort entity quarterly_date
	merge n:n entity quarterly_date using "$path/0 Data/_Processed/BHC_Bank_Ratios_20192020"
	rename quarterly_date exposuredate_y_q
	
	drop if _merge ==2 
	drop _merge
	
	sort entity
	merge n:n entity using "$path/0 Data/_Processed/channel ratios"
	
	drop if _merge == 2
	drop _merge
	
	rename gvkey gvkey_lender
	merge n:1 borrowercompanyid using "$path/0 Data/_Processed/Roberts-link"
	drop if _merge == 2
	drop _merge
	
	merge m:m gvkey using "$path/0 Data/_Processed/compustat-ratios"
	drop if _merge == 2
	drop _merge
	
	gen above_median_size = 0
	sum w_size, detail
	replace above_median_size = 1 if w_size > `r(p50)'
	replace above_median_size = . if w_size == .
	
	
	rename borrowercompanyid BorrowerId
	rename entity LenderParentId
	
	generate post = (exposuredate_y_q >= qofd(mdy(4,1,2020)))
	
	order BorrowerId LenderParentId exposuredate_y_q post 
	
	sort BorrowerId LenderParentId exposuredate_y_q	

	
	
	
	* construct measure of outstanding credit lines
	******************************************************
	
	gen unused_ci_assets = offbs_unused_loans/ assets_total
	
	
	
	
	
	
	
	* Collapse to borrower-bank-loantype-post pair
	*******************************************
	
	*drop 2020q1
	drop if exposuredate_y_q == 240
	
	*drop 2020q3 and 2020q4
	drop if exposuredate_y_q > 241
	
	
	******
	* Version with only originations
	*****
			
	egen panelid = group(BorrowerId LenderParentId term_loan)
	sort panelid post exposuredate_y_q
			
	collapse (sum) total_new_loan_origination (mean) exposuredate_y_q (mean) BorrowerId LenderParentId term_loan unused_ci_assets change_unused_ci_ta net_drawdowns change_deposits_ta liquidity_assets wholesale_assets_lag npl_loans_lag capital_lag nonintinc_lag log_assets_lag roa_lag deposits_loans_lag reg_tier1_ratio_lag income_diversity_lag log_Z_lag loans_assets_lag deposits_assets_lag fees_earned_j repay_j_repayers_scaled (max) above_median_size, by(panelid post)
	
	rename total_new_loan_origination exposure_outstanding	
	
	xtset panelid post
	sort  panelid post
	
	
	** Label variables for output
label var unused_ci_assets 				"Unused C&I Loans / Assets"
label var liquidity_assets 				"Liquidity / Assets"
label var wholesale_assets_lag 			"Wholesale Funding / Assets"
label var npl_loans_lag					"NPL / Loans"
label var capital_lag 					"Equity Ratio"
label var nonintinc_lag 				"Non-Interest Income"
label var log_assets_lag 				"Log(Assets)"
label var roa_lag 						"ROA"
label var deposits_loans_lag 			"Deposits / Loans"
label var reg_tier1_ratio_lag			"Tier-1 Ratio"
label var income_diversity_lag  		"Income Diversity"
label var log_Z_lag  					"Distance-to-Default"
label var loans_assets_lag 				"Loans / Assets"
label var deposits_assets_lag  			"Deposits / Assets"
label var net_drawdowns					"Net Drawdowns"
label var change_unused_ci_ta			"Gross Drawdowns"
label var change_deposits_ta			"Change in Deposits"
label var term_loan						"Term Loan Indicator"

	

	* assign missing zeros, as there are no loans
	replace exposure_outstanding			= 0 if exposure_outstanding 			==.		
	
	
	
	winsor exposure_outstanding, gen(amount_w) p(0.01)
	
	
	
	* Construct variables
	****************************	
	
	gen log_amount 		= log(amount_w)
	gen post_net 		= post * net_drawdowns
	gen post_gross 		= post * change_unused_ci_ta
	gen post_deposits   = post * change_deposits_ta
	
	
	gen capital_above_median = 0
	sum capital_lag, detail
	replace capital_above_median  = 1 if capital_lag > `r(p50)'
	replace capital_above_median  = . if capital_lag == .
	
	gen post_net_above_median = 0
	sum net_drawdowns, detail
	replace post_net_above_median  = 1 if net_drawdowns > `r(p50)'
	replace post_net_above_median  = 0 if post == 0
	replace post_net_above_median  = . if net_drawdowns == .

	
	gen post_gross_above_median = 0
	sum change_unused_ci_ta, detail
	replace post_gross_above_median  = 1 if change_unused_ci_ta > `r(p50)'
	replace post_gross_above_median  = 0 if post == 0
	replace post_gross_above_median  = . if change_unused_ci_ta == .
	
	
	gen post_deposits_above_median = 0
	sum change_deposits_ta, detail
	replace post_deposits_above_median  = 1 if change_deposits_ta > `r(p50)'
	replace post_deposits_above_median  = 0 if post == 0
	replace post_deposits_above_median  = . if change_deposits_ta == .
	
	
	gen post_fees_above_median = 0
	sum fees_earned_j, detail
	replace post_fees_above_median  = 1 if fees_earned_j > `r(p50)'
	replace post_fees_above_median  = 0 if post == 0
	replace post_fees_above_median  = . if fees_earned_j == .
	
	
	gen post_repay_above_median = 0
	sum repay_j_repayers_scaled, detail
	replace post_repay_above_median  = 1 if repay_j_repayers_scaled > `r(p50)'
	replace post_repay_above_median  = 0 if post == 0
	replace post_repay_above_median  = . if repay_j_repayers_scaled == .
	
	
	label var post_net						"Post Net"
	label var post_gross					"Post Gross"
	label var post_deposits					"Post Deposits"
	label var post_net_above_median			"Above Median Net x Post"
	label var post_gross_above_median		"Above Median Gross x Post"
	label var post_deposits_above_median	"Above Median Deposits x Post"
	
	
	bysort panelid: gen loan_growth = (amount_w-amount_w[_n-1])/amount_w[_n-1]
	bysort panelid: gen loan_growth_log = log(amount_w/amount_w[_n-1])

	
	global Controls npl_loans_lag log_assets_lag roa_lag reg_tier1_ratio_lag loans_assets_lag
	
	
	egen borrowerbank 	= group(BorrowerId LenderParentId)	

	
	* Table 6 columns 5-8
	*****************************
	

			
	eststo clear
	
	eststo, title("Model 1"): quietly reghdfe log_amount post_gross post_net, absorb(BorrowerId##post##term_loan borrowerbank) vce(cluster LenderParentId)
		
	eststo, title("Model 1"): quietly reghdfe log_amount post_gross_above_median post_net_above_median, absorb(BorrowerId##post##term_loan borrowerbank) vce(cluster LenderParentId)
		
	eststo, title("Model 1"): quietly reghdfe log_amount c.post_gross_above_median##c.term_loan c.post_net_above_median##c.term_loan, absorb(BorrowerId##post##term_loan borrowerbank) vce(cluster LenderParentId)
		
	eststo, title("Model 1"): quietly reghdfe log_amount c.post_gross_above_median##c.term_loan c.post_net_above_median##c.term_loan $Controls , absorb(BorrowerId##post##term_loan borrowerbank) vce(cluster LenderParentId)
	

	
	
	local 		date:	di %tdCYND daily("$S_DATE", "DMY")

	esttab using "$path/04 Results/`date'_Table6_cols5to8.csv", p(3) label star(* 0.10 ** 0.05 *** 0.01) stats(r2  N, labels(R-squared "Number obs."))  replace
		
	
			
*eof
	
	
